package com.chenyue.cm.match.dao.provider;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

/**
 * @author libingxing
 */
public class MatchDataCenterProvider {

    public String dataCenterList(Integer type, Integer item, Integer createUser, Integer matchType, Integer matchGroup,
                                 Integer arrangeOrder, Integer province, Integer city, String date,Integer matchId, String convertPage) {
        final boolean typeNotNull = type != null && type > 0;
        final boolean itemNotNull = item != null && item > 0;
        final boolean createUserNotNull = createUser != null && createUser > -1;
        final boolean matchTypeNotNull = matchType != null && matchType > 0;
        final boolean matchGroupNotNull = matchGroup != null && matchGroup > 0;
        final boolean arrangeOrderNotNull = arrangeOrder != null && arrangeOrder > 0;
        final boolean provinceNotNull = province != null && province > 0;
        final boolean cityNotNull = city != null && city > 0;
        final boolean matchIdNull = matchId != null && matchId >0;
        return new SQL() {
            {
                SELECT("r.user_id as userId");
                SELECT("r.user_name as userName");
                SELECT("r.head_img as headImg");
                SELECT("r.group_name as teamName");
                SELECT("r.group_id as teamId");
                SELECT("(select score from view_match_score_record where user_id = r.user_id" +
                        (typeNotNull ? " and type = #{type} " : "") +
                        (itemNotNull ? " and item = #{item} " : "") +
                        (createUserNotNull ? " and cu = #{createUser} " : "") +
                        (matchTypeNotNull ? " and match_type = #{matchType} " : "") +
                        (matchGroupNotNull ? " and match_group = #{matchGroup} " : "") +
                        (arrangeOrderNotNull ? " and arrange_order = #{arrangeOrder} " : "") +
                        (provinceNotNull ? " and province = #{province}" : " and isnull(province)") +
                        (cityNotNull ? " and r.city = #{city} " : " and isnull(r.city)") +
                        "order by score desc limit 1) as score");
                FROM("view_match_score_record r");
                if (typeNotNull) {
                    WHERE("r.type = #{type}");
                }
                if (itemNotNull) {
                    WHERE("r.item = #{item}");
                }
                if (createUserNotNull) {
                    WHERE("r.cu = #{createUser}");
                }
                if (matchTypeNotNull) {
                    WHERE("r.match_type = #{matchType}");
                }
                if (matchGroupNotNull) {
                    WHERE("r.match_group = #{matchGroup}");
                }
                if (arrangeOrderNotNull) {
                    WHERE("r.arrange_order = #{arrangeOrder}");
                }
                if (matchIdNull) {
                    WHERE("r.match_id = #{matchId}");
                }
                if (provinceNotNull) {
                    WHERE("r.province = #{province}");
                } else {
                    WHERE("isnull(r.province)");
                }
                if (cityNotNull) {
                    WHERE("(r.city = #{city})");
                } else {
                    WHERE("isnull(r.city)");
                }
                if (StringUtils.isNotBlank(date)) {
                    WHERE("r.ct <= #{date}");
                }
                GROUP_BY("r.user_id");
                ORDER_BY("score desc");
            }
        } + convertPage;
    }

    public String dataCenterCount(Integer type, Integer item, Integer createUser, Integer matchType, Integer matchGroup,
                                  Integer arrangeOrder, Integer province, Integer city, String date ,Integer matchId) {
        String sql = new SQL() {
            {
                SELECT("1");
                FROM("view_match_score_record r");
                if (type != null && type > 0) {
                    WHERE("r.type = #{type}");
                }
                if (item != null && item > 0) {
                    WHERE("r.item = #{item}");
                }
                if (createUser != null && createUser > -1) {
                    WHERE("r.cu = #{createUser}");
                }
                if (matchType != null && matchType > 0) {
                    WHERE("r.match_type = #{matchType}");
                }
                if (matchGroup != null && matchGroup > 0) {
                    WHERE("r.match_group = #{matchGroup}");
                }
                if (arrangeOrder != null && arrangeOrder > 0) {
                    WHERE("r.arrange_order = #{arrangeOrder}");
                }
                if (matchId != null && matchId >0){
                    WHERE("r.match_id = #{matchId}");
                }
                if (province != null && province > 0) {
                    WHERE("r.province = #{province}");
                } else {
                    WHERE("isnull(r.province)");
                }
                if (city != null && city > 0) {
                    WHERE("(r.city = #{city})");
                } else {
                    WHERE("isnull(r.city)");
                }
                if (StringUtils.isNotBlank(date)) {
                    WHERE("r.ct <= #{date}");
                }
                GROUP_BY("r.user_id");
            }
        } + "";
        return new SQL() {
            {
                SELECT("count(0)");
                FROM("(" + sql + ")v");
            }
        } + "";
    }



    public String selectMatchVo(Integer type,String starTime,String endTime,String s) {
        return new SQL(){
            {
                SELECT("jau.id,jau.head_img as avatar, vmsr.user_name as name , vmsr.group_name as ranks, sum( vmsr.score ) as fraction ");
                FROM("view_match_score_record vmsr ");
                LEFT_OUTER_JOIN("jd_match_type jmt on vmsr.type = jmt.id ");
                LEFT_OUTER_JOIN("jd_app_user jau on jau.id = vmsr.user_id ");
                if (ObjectUtils.isNotEmpty(type)){
                    WHERE("vmsr.type  = "+type );
                }
                if (StringUtils.isNotEmpty(starTime) && StringUtils.isNotEmpty(endTime)){
                    WHERE("vmsr.ct >'"+starTime+"' and vmsr.ct< '"+endTime+"'");
                }
                GROUP_BY("vmsr.user_id");
                ORDER_BY("sum(vmsr.score) DESC ");
            }
        }+s;
    }

    public String selectMatchVoCount(Integer type,String starTime,String endTime){
        return new SQL(){
            {
                SELECT("jau.id,jau.head_img as avatar, vmsr.user_name as name , vmsr.group_name as ranks, sum( vmsr.score ) as fraction ");
                FROM("view_match_score_record vmsr ");
                LEFT_OUTER_JOIN("jd_match_type jmt on vmsr.type = jmt.id ");
                LEFT_OUTER_JOIN("jd_app_user jau on jau.id = vmsr.user_id ");
                if (ObjectUtils.isNotEmpty(type)){
                    WHERE("vmsr.type  = "+type );
                }
                if (StringUtils.isNotEmpty(starTime) && StringUtils.isNotEmpty(endTime)){
                    WHERE("vmsr.ct >'"+starTime+"' and vmsr.ct< '"+endTime+"'");
                }
                GROUP_BY("vmsr.user_id");
                ORDER_BY("sum(vmsr.score) DESC ");
            }
        }.toString();
    }


}
